<?xml version="1.0" encoding="utf-8"?>
<!--
                                                                                     
 h       t     t                ::       /     /                     t             / 
 h       t     t                ::      //    //                     t            // 
 h     ttttt ttttt ppppp sssss         //    //  y   y       sssss ttttt         //  
 hhhh    t     t   p   p s            //    //   y   y       s       t          //   
 h  hh   t     t   ppppp sssss       //    //    yyyyy       sssss   t         //    
 h   h   t     t   p         s  ::   /     /         y  ..       s   t    ..   /     
 h   h   t     t   p     sssss  ::   /     /     yyyyy  ..   sssss   t    ..   /     
                                                                                     
	<https://y.st./>
	Copyright © 2017 Alex Yst <mailto:copyright@y.st>

	This program is free software: you can redistribute it and/or modify
	it under the terms of the GNU General Public License as published by
	the Free Software Foundation, either version 3 of the License, or
	(at your option) any later version.

	This program is distributed in the hope that it will be useful,
	but WITHOUT ANY WARRANTY; without even the implied warranty of
	MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
	GNU General Public License for more details.

	You should have received a copy of the GNU General Public License
	along with this program. If not, see <https://www.gnu.org./licenses/>.
-->
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
	<head>
		<base href="https://y.st./en/coursework/CS2203/Library_database_SQL.xhtml" />
		<title>Library database SQL &lt;https://y.st./en/coursework/CS2203/Library_database_SQL.xhtml&gt;</title>
		<link rel="icon" type="image/png" href="/link/CC_BY-SA_4.0/y.st./icon.png" />
		<link rel="stylesheet" type="text/css" href="/link/basic.css" />
		<link rel="stylesheet" type="text/css" href="/link/site-specific.css" />
		<script type="text/javascript" src="/script/javascript.js" />
		<meta name="viewport" content="width=device-width" />
	</head>
	<body>
		<nav>
			<p>
				<a href="/en/">Home</a> |
				<a href="/en/a/about.xhtml">About</a> |
				<a href="/en/a/contact.xhtml">Contact</a> |
				<a href="/a/canary.txt">Canary</a> |
				<a href="/en/URI_research/"><abbr title="Uniform Resource Identifier">URI</abbr> research</a> |
				<a href="/en/opinion/">Opinions</a> |
				<a href="/en/coursework/">Coursework</a> |
				<a href="/en/law/">Law</a> |
				<a href="/en/a/links.xhtml">Links</a> |
				<a href="/en/coursework/CS2203/Library_database_SQL.xhtml.asc">{this page}.asc</a>
			</p>
			<hr/>
			<p>
				<a href="/en/coursework/BUS1101/" title="Principles of Business Management">BUS 1101</a> |
				<span class="hyperlink_unavailable" title="Basic Accounting">BUS 1102</span> |
				<span class="hyperlink_unavailable" title="Principles of Marketing">BUS 2201</span> |
				<span class="hyperlink_unavailable" title="Multinational Management">BUS 2207</span> |
				<span class="hyperlink_unavailable" title="Business and Society">BUS 3306</span> |
				<a href="/en/coursework/CS1101/" title="Programming Fundamentals">CS 1101</a> |
				<a href="/en/coursework/CS1102/" title="Programming 1">CS 1102</a> |
				<a href="/en/coursework/CS1103/" title="Programming 2">CS 1103</a> |
				<span class="hyperlink_unavailable" title="Computer Systems">CS 1104</span> |
				<a href="/en/coursework/CS2203/" title="Databases 1">CS 2203</a> |
				<span class="hyperlink_unavailable" title="Communications and Networking">CS 2204</span> |
				<a href="/en/coursework/CS2205/" title="Web Programming 1">CS 2205</a> |
				<a href="/en/coursework/CS2301/" title="Operating Systems 1">CS 2301</a> |
				<span class="hyperlink_unavailable" title="Software Engineering 1">CS 2401</span> |
				<span class="hyperlink_unavailable" title="Data Structures">CS 3303</span> |
				<span class="hyperlink_unavailable" title="Analysis of Algorithms">CS 3304</span> |
				<span class="hyperlink_unavailable" title="Web Programming 2">CS 3305</span> |
				<span class="hyperlink_unavailable" title="Databases 2">CS 3306</span> |
				<span class="hyperlink_unavailable" title="Operating Systems 2">CS 3307</span> |
				<span class="hyperlink_unavailable" title="Information Retrieval">CS 3308</span> |
				<span class="hyperlink_unavailable" title="Comparative Programming Languages">CS 4402</span> |
				<span class="hyperlink_unavailable" title="Software Engineering 2">CS 4403</span> |
				<span class="hyperlink_unavailable" title="Advanced Networking and Data Security">CS 4404</span> |
				<span class="hyperlink_unavailable" title="Mobile Applications">CS 4405</span> |
				<span class="hyperlink_unavailable" title="Computer Graphics">CS 4406</span> |
				<span class="hyperlink_unavailable" title="Data Mining and Machine Learning">CS 4407</span> |
				<span class="hyperlink_unavailable" title="Artificial Intelligence">CS 4408</span> |
				<span class="hyperlink_unavailable" title="English Composition 2">ENGL 1102</span> |
				<span class="hyperlink_unavailable" title="World Literature">ENGL 1405</span> |
				<span class="hyperlink_unavailable" title="Introduction to Environmental Science">ENVS 1301</span> |
				<a href="/en/coursework/HIST1421/" title="Greek and Roman Civilization">HIST 1421</a> |
				<span class="hyperlink_unavailable" title="College Algebra">MATH 1201</span> |
				<span class="hyperlink_unavailable" title="Calculus">MATH 1211</span> |
				<span class="hyperlink_unavailable" title="Introduction to Statistics">MATH 1280</span> |
				<span class="hyperlink_unavailable" title="Discrete Mathematics">MATH 1302</span> |
				<span class="hyperlink_unavailable" title="Introduction to Philosophy">PHIL 1402</span> |
				<a href="/en/coursework/PHIL1404/" title="Ethics and Social Responsibility">PHIL 1404</a> |
				<a href="/en/coursework/POLS1503/" title="Globalization">POLS 1503</a> |
				<span class="hyperlink_unavailable" title="Introduction to Psychology">PSYC 1504</span> |
				<a href="/en/coursework/UNIV1001/" title="Online Education Strategies">UNIV 1001</a>
			</p>
			<hr/>
			<p>
				<a href="/en/coursework/CS2203/Library_database_relations.xhtml" title="Library database relations">Unit 1</a> |
				<a href="/en/coursework/CS2203/Library_database_relations~_continued.xhtml" title="Library database relations, continued">Unit 2</a> |
				<a href="/en/coursework/CS2203/Library_database_relation_diagram.xhtml" title="Library database relation diagram">Unit 3</a> |
				<a href="/en/coursework/CS2203/Library_database_normalisation.xhtml" title="Library database normalisation">Unit 4</a> |
				<a href="/en/coursework/CS2203/Library_database_SQL.xhtml" title="Library database SQL">Unit 5</a> |
				<a href="/en/coursework/CS2203/Library_database_SQL~_continued.xhtml" title="Library database SQL, continued">Unit 6</a> |
				<a href="/en/coursework/CS2203/Library_database_SQL_select.xhtml" title="Library database SQL select">Unit 7</a>
			</p>
			<hr/>
		</nav>
		<header>
			<h1>Library database <abbr title="Structured Query Language">SQL</abbr></h1>
			<p>Written in <span title="Databases 1">CS 2203</span> of <a href="http://www.uopeople.edu/">University of the People</a>, finalised on 2017-07-19</p>
		</header>
<h2><abbr title="Structured Query Language">SQL</abbr> commands</h2>
<blockquote>
<pre><code>create table Borrower (
	card_number int generated by default as identity (start with 0, increment by 1) not null primary key,
	name varchar(32) not null,
	address varchar(32) not null,
	postal_code int not null,
	telephone_number int,
	join_date date default current_date not null,
	check (postal_code &lt; 100000)
);
create table Librarian (
	id int generated by default as identity (start with 0, increment by 1) not null primary key,
	name varchar(32) not null,
	extension int not null,
	reports_to int,
	foreign key (reports_to) references Librarian(id)
);
create table BookMeta (
	isbn bigint not null primary key,
	title varchar(64) not null,
	author varchar(32) not null,
	publication_date date not null,
	check (isbn &lt; 10000000000000)
);
create table Book(
	id int generated by default as identity (start with 0, increment by 1) not null primary key,
	isbn bigint not null,
	cost decimal(5,2) not null,
	foreign key (isbn) references BookMeta(isbn)
);
create table BookLended(
	book int not null primary key,
	checked_out date default current_date not null,
	due_back date not null,
	borrower int not null,
	librarian int,
	check (due_back &gt; checked_out),
	foreign key (book) references Book(id),
	foreign key (borrower) references Borrower(card_number),
	foreign key (librarian) references Librarian(id)
);</code></pre>
</blockquote>
<h2>Screenshot</h2>
<img src="/img/CC_BY-SA_4.0/y.st./coursework/CS2203/Library_database_SQL.png" alt="Database objects" class="weblog-header-image" width="623" height="356" />
<h2>Explanations</h2>
<p>
	On the <code>Borrower</code> table, the <code>card_number</code> is set to auto-increment so we don&apos;t have to use an external system to assign card numbers.
	The <code>telephone_number</code> is allowed to be null, because some people (such as myself) don&apos;t have telephone service and therefore don&apos;t have a telephone number.
	It&apos;s a pain dealing with companies that insist that you need a telephone number, so in my implementation of the library, I gave people like me a break.
	If a borrower doesn&apos;t have a telephone number, the database can accommodate that.
	The <code>postal_code</code> has a constraint that prevents postal codes with more than five digits from being entered, as postal codes are five digits long.
</p>
<p>
	Like <code>Borrower</code> <code>card_number</code>s, <code>Librarian</code> <code>id</code>s are assigned automatically.
	The telephone <code>extension</code>, unlike <code>Borrower</code> <code>telephone_number</code>, is not allowed to be null.
	This is because all librarians in the building will have an extension using the library&apos;s telephone service.
	We can manually add a <code>join_date</code> if we want, but it defaults to the current date.
	This is because we&apos;re probably adding them to our database as soon as they register an account with the library.
	The unary relationship we defined in class is included.
	The assignment instructions said we don&apos;t need it, but they didn&apos;t explicitly say <strong>*not*</strong> to include it, and including it was actually quite easy.
</p>
<p>
	The <code>BookMeta</code> table was added to remove redundancy in the <code>Book</code> table.
	Each copy of a book needs its own entry in the <code>Book</code> table for a few reasons.
	First, differing copies might have been bought for different prices, so the <code>cost</code> needs to be independent for each copy.
	Second, each copy needs to be tracked separately in the <code>BookLended</code> relation.
	However, all copies of the same book will have the same <abbr title="International Standard Book Number">ISBN</abbr>, publication date, author, and title.
	A check is performed on the <code>isbn</code> field to ensure it&apos;s not longer than thirteen digits, as while <abbr title="International Standard Book Number">ISBN</abbr>s have a few different lengths, none are longer than thirteen digits.
</p>
<p>
	The <code>Book</code> relation references the <code>BookMeta</code> relation for most of its information, but gives a separate <code>id</code> to each copy of a book and holds the <code>cost</code> of that copy.
	<code>id</code>s are mostly meaningless outside the database, so they&apos;re simply assigned automatically.
	Once the <code>id</code>s are assigned, barcodes for them can be generated and put on the library&apos;s books.
</p>
<p>
	The <code>BookLended</code> table ties the three other main tables together.
	The <code>book</code> attribute acts as both a primary key and a foreign key.
	As a foreign key, it tells us which book was lent out.
	It works as a primary key too though because a book cannot be lent out to two people at once.
	When the book is returned, the tuple representing that lending will be removed, and the book can be lent out again.
	<code>book</code> and <code>borrower</code> cannot be null, as there can&apos;t be a lending of a book if there is no book lent and/or no one borrowing it.
	If the book is removed from the collection, the lending information should be dropped too.
	If we want to remove a borrower from the system, we should first have them check the book back in or we should strike any books they haven&apos;t returned from the system.
	As long as we&apos;re keeping the books, even if we disallow that patron from borrowing any more, we need to keep that former patron&apos;s information in the database so we know who has our book.
	<code>librarian</code> <strong>*can*</strong> be null though.
	If we fire a librarian and remove them from the database, the books that they lent out before are still out there, and we need to know which books are missing and who has them.
	When a book is checked out, <code>checked_out</code> is automatically set to the current date, unless we specify otherwise.
	A book cannot be <code>due_back</code> before it is checked out though, so a constraint ensures this basic logic is followed.
	<code>due_back</code> isn&apos;t automatically set though, as the instructions never told us how long a book could be checked out for.
	Additionally, there could be multiple factors.
	A long-time patron may be allowed to check books out for longer or a popular book may be due back sooner.
	By not automatically setting a due date, we allow for flexibility.
</p>
		<hr/>
		<p>
			Copyright © 2017 Alex Yst;
			You may modify and/or redistribute this document under the terms of the <a rel="license" href="/license/gpl-3.0-standalone.xhtml"><abbr title="GNU&apos;s Not Unix">GNU</abbr> <abbr title="General Public License version Three or later">GPLv3+</abbr></a>.
			If for some reason you would prefer to modify and/or distribute this document under other free copyleft terms, please ask me via email.
			My address is in the source comments near the top of this document.
			This license also applies to embedded content such as images.
			For more information on that, see <a href="/en/a/licensing.xhtml">licensing</a>.
		</p>
		<p>
			<abbr title="World Wide Web Consortium">W3C</abbr> standards are important.
			This document conforms to the <a href="https://validator.w3.org./nu/?doc=https%3A%2F%2Fy.st.%2Fen%2Fcoursework%2FCS2203%2FLibrary_database_SQL.xhtml"><abbr title="Extensible Hypertext Markup Language">XHTML</abbr> 5.1</a> specification and uses style sheets that conform to the <a href="http://jigsaw.w3.org./css-validator/validator?uri=https%3A%2F%2Fy.st.%2Fen%2Fcoursework%2FCS2203%2FLibrary_database_SQL.xhtml"><abbr title="Cascading Style Sheets">CSS</abbr>3</a> specification.
		</p>
	</body>
</html>

